﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Dragon
{    
    public class DataService
    {
        string conString = ConfigurationManager.ConnectionStrings["CarlsbergConnectionString"].ConnectionString;
        SqlConnection sqlCon;
        SqlTransaction sqlTrans;

        public DataService()
        {
            sqlCon = new SqlConnection(conString);    
        }

        public SqlConnection Connnection
        {
            set { sqlCon = value; }
            get { return sqlCon; }
        }

        void OpenConnection()
        {           
            sqlCon.Open();
        }

        void CloseConnection()
        {
            sqlCon.Close();
        }
          

        public DataTable GetData(string sql, params SqlParameter[] param)
        {
            DataTable result = new DataTable();
            try
            {
                OpenConnection();
                SqlCommand sqlCom = new SqlCommand(sql, sqlCon);
                sqlCom.Parameters.AddRange(param);
                result.Load(sqlCom.ExecuteReader());
                CloseConnection();
                return result;
            }
            catch (Exception ex)
            {
                CloseConnection();
                return result;
            }
        }

        public void SetData(string sql, params SqlParameter[] param)
        {
            try
            {
                OpenConnection();
                sqlTrans = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted);
                SqlCommand sqlCom = new SqlCommand(sql, sqlCon);
                sqlCom.Transaction = sqlTrans;
                sqlCom.Parameters.AddRange(param);
                sqlCom.ExecuteNonQuery();
                sqlTrans.Commit();
                CloseConnection();
            }
            catch(Exception ex)
            {
                sqlTrans.Commit();
                CloseConnection();
                return;
            }
        }
    }
}